﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class DangKyDeTaiRepository:IDangKyDeTaiRepository
    {
        public DangKyDeTaiDTO InsertDangKyDeTai(DangKyDeTaiDTO dangKyDeTaiDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTaiInsert]", GetParameterArray(dangKyDeTaiDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToDangKyDeTaiDTO();
            }
            catch (Exception ex)
            {
                return null;
            }        
        }
        public DangKyDeTaiDTO UpdateDangKyDeTai(DangKyDeTaiDTO dangKyDeTaiDTO) 
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTaiUpdate]", GetParameterArray(dangKyDeTaiDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToDangKyDeTaiDTO();
            }
            catch (Exception ex)
            {
                return null;
            }        
        }
        public DangKyDeTaiDTO GetDangKyDeTaiById(Guid maDangKyDeTai)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTaiSelect]", DBParameterFactory.Create("@MaDangKyDeTai", maDangKyDeTai));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToDangKyDeTaiDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public DangKyDeTaiDTO GetDangKyDeTaiByDeTaiId(Guid maDeTai)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("MaDeTai", maDeTai);
            parameters[1] = DBParameterFactory.Create("MaNamHoc", SqlDbType.UniqueIdentifier, DBNull.Value);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTai_MaDeTai_Select]", parameters);
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToDangKyDeTaiDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<DangKyDeTaiDTO> GetAllDangKyDeTai() 
        {
            List<DangKyDeTaiDTO> deTaiList = new List<DangKyDeTaiDTO>();
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("MaDangKyDeTai", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[1] = DBParameterFactory.Create("ManamHoc", SqlDbType.UniqueIdentifier, DBNull.Value);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTaiSelect]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    deTaiList.Add(dataRow.ToDangKyDeTaiDTO());
                }
                return deTaiList.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }               
        }
        public IEnumerable<DangKyDeTaiDTO> GetAllDangKyDeTaiNamHoc(Guid MaNamHoc)
        {
            List<DangKyDeTaiDTO> deTaiList = new List<DangKyDeTaiDTO>();
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("MaDangKyDeTai",SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[1] = DBParameterFactory.Create("ManamHoc", MaNamHoc);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTaiSelect]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    deTaiList.Add(dataRow.ToDangKyDeTaiDTO());
                }
                return deTaiList.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<DangKyDeTaiDTO> GetDangKyDeTaiBySinhVienId(Guid SinhVienid,Guid MaNamHoc)
        {
            List<DangKyDeTaiDTO> deTaiList = new List<DangKyDeTaiDTO>();
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("SinhVienDangKy", SinhVienid);
            parameters[1] = DBParameterFactory.Create("MaNamHoc", MaNamHoc);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTaiSinhVienSelect]",parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    deTaiList.Add(dataRow.ToDangKyDeTaiDTO());
                }
                return deTaiList.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public IEnumerable<DangKyDeTaiDTO> GetDangKyDeTaiByMSSV(String MaSinhVien, Guid MaNamHoc)
        {
            List<DangKyDeTaiDTO> deTaiList = new List<DangKyDeTaiDTO>();
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("MaSinhVien", MaSinhVien);
            parameters[1] = DBParameterFactory.Create("MaNamHoc", MaNamHoc);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_DangKyDeTaiMSSVSelect]", parameters);
            try
            {
                var caTable = dbResult.Tables[0].Rows;
                foreach (DataRow dataRow in caTable)
                {
                    deTaiList.Add(dataRow.ToDangKyDeTaiDTO());
                }
                return deTaiList.AsEnumerable();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public bool DeleteDangKyDeTai(Guid maDangKyDeTai)
        {
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_DangKyDeTaiDelete]", DBParameterFactory.Create("@MaDangKyDeTai", maDangKyDeTai));       
        }

        #region Private Methods
        private System.Data.SqlClient.SqlParameter[] GetParameterArray(DangKyDeTaiDTO dangKyDeTaiDTO)
        {
            SqlParameter[] parameters = new SqlParameter[7];
            parameters[0] = DBParameterFactory.Create("MaDangKyDeTai", dangKyDeTaiDTO.MaDangKyDeTai);
            parameters[1] = DBParameterFactory.Create("MaDeTai", dangKyDeTaiDTO.MaDeTai);
            parameters[2] = DBParameterFactory.Create("MaNamHoc", dangKyDeTaiDTO.MaNamHoc);
            parameters[3] = DBParameterFactory.Create("SinhVienDangKy", dangKyDeTaiDTO.SinhVienDangKy);
            parameters[4] = DBParameterFactory.Create("MaSinhVien1", dangKyDeTaiDTO.MaSinhVien1);
            parameters[5] = DBParameterFactory.Create("MaSinhVien2", dangKyDeTaiDTO.MaSinhVien2);
            parameters[6] = DBParameterFactory.Create("NgayDangKy", dangKyDeTaiDTO.NgayDangKy);              
            return parameters;
        }
        #endregion
    }
}
